package com.whzykj.model;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;
import com.whzykj.jfinal.TableBind;

@TableBind(name = "account_seq", pk = "sn")
public class AccountSeq extends Model<AccountSeq> {
	private static final long serialVersionUID = 1L;
	public static final AccountSeq dao = new AccountSeq();
	
	/**
	 * 查询对账列表
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @return
	 */
	public List<Record> getAssignNewList(String startDate,String endDate,String stationid){
		List<Record> bList=getAssignBaseInfoList(stationid);//客户信息
		List<Record> acctList=findAssignAcctList(startDate,endDate);//账务往来信息
		List<Record> resList=dealAssignNewList(acctList,bList, startDate, endDate);
		return resList;
	}
	
	/**
	 * 处理财务对账列表，进行分站统计
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @return
	 */
	public List<Record> getFinanceCheckList(String startDate,String endDate,String stationid){
		List<Record> recList=getAssignNewList(startDate, endDate,String.valueOf(stationid));
		List<Record> newList=null;
		if(recList!=null && recList.size()>0){
			newList=new ArrayList<Record>();
			Record rec1=recList.get(0);
			Record rec2=null;
			newList.add(rec1);	
			double t0=Double.valueOf(rec1.getStr("00str").substring(1));
			double t1=Double.valueOf(rec1.getStr("01str").substring(1));
			double t2=Double.valueOf(rec1.getStr("02str").substring(1));
			double t3=Double.valueOf(rec1.getStr("03str").substring(1));
			double t4=Double.valueOf(rec1.getStr("04str").substring(1));
			double t5=Double.valueOf(rec1.getStr("05str").substring(1));
			double t6=Double.valueOf(rec1.getStr("06str").substring(1));
			double total=Double.valueOf(rec1.getStr("total").substring(1));
			DecimalFormat df=new DecimalFormat("#0.00");
			Record record=null;
			for(int i=1;i<recList.size();i++){
				rec2=recList.get(i);
				if(rec1.getStr("station_name").equals(rec2.getStr("station_name"))){
					t0=t0+Double.valueOf(rec2.getStr("00str").substring(1));
					t1=t1+Double.valueOf(rec2.getStr("01str").substring(1));
					t2=t2+Double.valueOf(rec2.getStr("02str").substring(1));
					t3=t3+Double.valueOf(rec2.getStr("03str").substring(1));
					t4=t4+Double.valueOf(rec2.getStr("04str").substring(1));
					t5=t5+Double.valueOf(rec2.getStr("05str").substring(1));
					t6=t0+Double.valueOf(rec2.getStr("06str").substring(1));
					total=total+Double.valueOf(rec2.getStr("total").substring(1));
				}else{
					record=new Record();
					record.set("station_name",rec1.getStr("station_name"));
					record.set("station_master",rec1.getStr("station_master"));
					record.set("cust_name","站点合计");
					record.set("mobile","");
					record.set("00str","￥"+df.format(t0));
					record.set("01str","￥"+df.format(t1));
					record.set("02str","￥"+df.format(t2));
					record.set("03str","￥"+df.format(t3));
					record.set("04str","￥"+df.format(t4));
					record.set("05str","￥"+df.format(t5));
					record.set("06str","￥"+df.format(t6));
					record.set("total","￥"+df.format(total));
					newList.add(record);
					record=null;
					t0=Double.valueOf(rec2.getStr("00str").substring(1));
					t1=Double.valueOf(rec2.getStr("01str").substring(1));
					t2=Double.valueOf(rec2.getStr("02str").substring(1));
					t3=Double.valueOf(rec2.getStr("03str").substring(1));
					t4=Double.valueOf(rec2.getStr("04str").substring(1));
					t5=Double.valueOf(rec2.getStr("05str").substring(1));
					t6=Double.valueOf(rec2.getStr("06str").substring(1));
					total=Double.valueOf(rec2.getStr("total").substring(1));
					rec1=rec2;
				}
				newList.add(rec2);
			}
		}
		return newList;
	}
		
	/**
	 * 查询符合条件的客户的基本信息
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @param status
	 * @return
	 */
	private List<Record> getAssignBaseInfoList(String stationid){
		List<Record> custList=null;
		StringBuffer sql=new StringBuffer(" SELECT sta.STATION_NAME,CONCAT(ui.USERNAME,ui.MOBILE) AS STATION_MASTER,cust.CUST_NAME,cust.MOBILE,cust.CUST_ID,acct.acct_id ");
		sql.append(" FROM customer cust INNER JOIN account acct ON cust.CUST_ID=acct.cust_id ");
		sql.append(" INNER JOIN station sta on sta.STATION_ID=cust.TAKE_STATION_ID");
		sql.append(" INNER JOIN userinfo ui ON ui.USERID=sta.STATION_MASTER");
		if(stationid!=null && !"-1".equals(stationid)){
			sql.append(" and sta.STATION_ID="+stationid);
		}
		sql.append(" order by sta.STATION_NAME,cust.CUST_ID");
		custList=Db.find(sql.toString());
		return custList;
	}
	
	/**
	 * 查询符合条件的订单商品的汇总信息
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @param status
	 * @return
	 */
	private List<Record> findAssignAcctList(String startDate, String endDate) {
		List<Record> list = null;
		StringBuffer sql = new StringBuffer(
				" SELECT oi.ACCT_ID,oi.change_type,SUM(oi.amount)-SUM(oi.pre_amount) AS amount,DATE_FORMAT(oi.create_time,'%Y-%m-%d') AS dates  FROM  account_seq oi WHERE 1=1 AND oi.seq_flag='00'  ");
		sql.append(" AND oi.change_type IN ('00','01','02','03','04','05','06') ");
		
		if (startDate != null && !startDate.equals("")) {
			sql.append(" and TO_DAYS(oi.create_time)>=TO_DAYS('" + startDate
					+ "')");
		}
		if (endDate != null && !endDate.equals("")) {
			sql.append(" and TO_DAYS(oi.create_time)<=TO_DAYS('" + endDate
					+ "')");
		}
		sql.append(" GROUP BY oi.ACCT_ID,oi.change_type ORDER BY oi.acct_id ASC ");
		list = Db.find(sql.toString());
		return list;
	}
	
	/*****
	 * 纵表转成横表
	 * @param startDate
	 * @param endDate
	 * @param acctid
	 * @return
	 */
	private List<Record> findAssignAcctList(String startDate, String endDate,String acctid) {
		List<Record> list = null;
		StringBuffer sql = new StringBuffer(" SELECT aa.dates, ");
		sql.append(" SUM(CASE aa.change_type WHEN  00 THEN aa.amount ELSE 0 END) AS str0, ");
		sql.append(" SUM(CASE aa.change_type WHEN  01 THEN aa.amount ELSE 0 END) AS str1, ");
		sql.append(" SUM(CASE aa.change_type WHEN  02 THEN aa.amount ELSE 0 END) AS str2, ");
		sql.append(" SUM(CASE aa.change_type WHEN  03 THEN aa.amount ELSE 0 END) AS str3, ");
		sql.append(" SUM(CASE aa.change_type WHEN  04 THEN aa.amount ELSE 0 END) AS str4, ");
		sql.append(" SUM(CASE aa.change_type WHEN  05 THEN aa.amount ELSE 0 END) AS str5, ");
		
		sql.append(" SUM(CASE aa.change_type WHEN  06 THEN aa.amount ELSE 0 END) AS str6 ");
		sql.append(" FROM (SELECT oi.change_type,SUM(oi.amount)-SUM(oi.pre_amount) AS amount,DATE_FORMAT(oi.create_time,'%Y-%m-%d') AS dates  FROM  account_seq oi WHERE 1=1 AND seq_flag='00' ");
		sql.append(" AND change_type IN ('00','01','02','03','04','05','06') AND acct_id="+acctid+" ");
		
		if (startDate != null && !startDate.equals("")) {
			sql.append(" and TO_DAYS(oi.create_time)>=TO_DAYS('" + startDate
					+ "')");
		}
		if (endDate != null && !endDate.equals("")) {
			sql.append(" and TO_DAYS(oi.create_time)<=TO_DAYS('" + endDate
					+ "')");
		}
		sql.append(" GROUP BY ACCT_ID,dates,change_type ORDER BY oi.acct_id ASC) aa GROUP BY aa.dates ");
		list = Db.find(sql.toString());
		return list;
	}
	
	
	/**
	 * 处理配送清单列表
	 * @param acctList
	 * @param bList
	 * @return
	 */
    private List<Record> dealAssignNewList(List<Record> acctList,List<Record> bList,String startDate,String endDate){
		
		List<Record> recList=new ArrayList<Record>();
		BigDecimal str0q=BigDecimal.ZERO;
		BigDecimal str1q=BigDecimal.ZERO;
		BigDecimal str2q=BigDecimal.ZERO;
		BigDecimal str3q=BigDecimal.ZERO;
		BigDecimal str4q=BigDecimal.ZERO;
		BigDecimal str5q=BigDecimal.ZERO;
		BigDecimal str6q=BigDecimal.ZERO;
		BigDecimal str7q=BigDecimal.ZERO;
		
		if(bList!=null && bList.size()>0){
			for(int i=0,size=bList.size();i<size;i++){
				Record custrd=bList.get(i);		
				int t_acct_id =custrd.getInt("acct_id");
				List<Record> accrd =  this.findAssignAcctList( startDate,  endDate, String.valueOf(t_acct_id));
				for(Record r:accrd){
					
					Record rec1=new Record();
					rec1.set("STATION_NAME", custrd.getStr("STATION_NAME"));
					rec1.set("STATION_MASTER", custrd.getStr("STATION_MASTER"));
					rec1.set("CUST_NAME", custrd.getStr("CUST_NAME"));
					rec1.set("MOBILE", custrd.getStr("MOBILE"));
					
					rec1.set("create_time", r.getStr("dates"));
					
					str0q=r.getBigDecimal("str0").add(str0q);
					str1q=r.getBigDecimal("str1").add(str1q);
					str2q=r.getBigDecimal("str2").add(str2q);
					str3q=r.getBigDecimal("str3").add(str3q);
					str4q=r.getBigDecimal("str4").add(str4q);
					str5q=r.getBigDecimal("str5").add(str5q);
					str6q=r.getBigDecimal("str6").add(str6q);
					str7q=r.getBigDecimal("str0").add(r.getBigDecimal("str1").add(r.getBigDecimal("str2"))).add(r.getBigDecimal("str3")).add(r.getBigDecimal("str4")).add(r.getBigDecimal("str5")).add(r.getBigDecimal("str6")).add(str7q);
					
					
					rec1.set("00str", "￥"+r.getBigDecimal("str0"));
					rec1.set("01str", "￥"+r.getBigDecimal("str1"));
					rec1.set("02str", "￥"+r.getBigDecimal("str2"));
					rec1.set("03str", "￥"+r.getBigDecimal("str3"));
					rec1.set("04str", "￥"+r.getBigDecimal("str4"));
					rec1.set("05str", "￥"+r.getBigDecimal("str5"));
					rec1.set("06str", "￥"+r.getBigDecimal("str6"));
					rec1.set("total", "￥"+r.getBigDecimal("str0").add(r.getBigDecimal("str1").add(r.getBigDecimal("str2"))).add(r.getBigDecimal("str3")).add(r.getBigDecimal("str4")).add(r.getBigDecimal("str5")).add(r.getBigDecimal("str6")));
					recList.add(rec1);
					
				}
			}
		}
		
		recList.add(new Record().set("STATION_NAME", "  ").set("STATION_MASTER", "  ").set("CUST_NAME", "汇总统计").set("MOBILE", "").set("00str", "￥"+str0q).set("01str", "￥"+str1q).set("02str", "￥"+str2q).set("03str", "￥"+str3q).set("04str", "￥"+str4q).set("05str", "￥"+str5q).set("06str", "￥"+str6q).set("total", "￥"+str7q));		// 	增加全站汇总统计信息
		return recList;
	}
	
    /**
	 * 查找每条商品对应的站点和客户基本信息
	 * @param acctList
	 * @param cust_id
	 * @return
	 */
	private List<Record> findBaseRecordByCustId(List<Record> acctList,int acct_id){
        List<Record> accList = new ArrayList<Record>();
		if(acctList!=null && acctList.size()>0){
			for(Record r:acctList){
				if(r.getInt("acct_id")==acct_id){
					accList.add(r);
				}
			}
		}
		return accList;
	}
	
	/**
	 * 查找每条商品对应的站点和客户基本信息
	 * @param acctList
	 * @param cust_id
	 * @return
	 */
	private Record findBaseRecordByCustId(List<Record> acctList,int acct_id,String flag){
		Record rec=null;
		if(acctList!=null && acctList.size()>0){
			for(Record r:acctList){
				if(r.getInt("acct_id")==acct_id && r.getStr("change_type").equals(flag)){
					rec=r;
					break;
				}
			}
		}
		return rec;
	}
	
}
